import configparser
import json
import os
import random
import time
import urllib.request

import pandas as pd
import pymysql
import redis
import requests
from pathlib import Path

import urllib3

urllib3.disable_warnings()

dataList = []

cookieVal = ""

redisClient = redis.Redis(host='10.1.100.117', port=6379, db=6, decode_responses=True)

db = pymysql.connect(
    host='10.1.100.122',
    port=3306,
    user='mydatabase_cs',
    passwd='Dtb5deOdBFebseUHC!HVN',
    db='test',
    charset='utf8'
)
# 游标设置为字典类型
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)

headersChoice = [
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv2.0.1) Gecko/20100101 Firefox/4.0.1",
    "Mozilla/5.0 (Windows NT 6.1; rv2.0.1) Gecko/20100101 Firefox/4.0.1",
    "Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; en) Presto/2.8.131 Version/11.11",
    "Opera/9.80 (Windows NT 6.1; U; en) Presto/2.8.131 Version/11.11",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 ",
    "Safari/535.11 ",
    "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36"
]


def requestCategory():
    url = "https://www.abcyun.cn/api/v2/sc/cdss/medicine/category?1702000066254"
    params = {}
    # print(params)
    headers = {
        "Cookie": "uuid=96d48a556bb74cc5933e675387093fc2; Hm_lvt_0e76138257ab1ae8bc1a9375b4180e64=1701757633; __root_domain_v=.abcyun.cn; _qddaz=QD.619501757633326; v2_remember_login=TRUE; grayflag=; grayflag=; Qs_lvt_359035=1701757633%2C1702949563%2C1706254064; Qs_pv_359035=777405993795203700%2C1627377586031348000%2C1580138439060517000%2C4135517799293870000%2C39527485949813740; _global_token_=eyJhbGciOiJIUzI1NiJ9.eyJlbXBsb3llZUlkIjoiZmZmZmZmZmYwMDAwMDAwMDEzNDRiMWQ4MDZkYWEwMDAiLCJlbXBsb3llZVNob3J0SWQiOiIxMzg4NDMwMTI2NTIyOTk4Nzg0IiwiZW1wbG95ZWVOYW1lIjoi6ams5Yab6IejIiwibG9naW5XYXkiOiJwYXNzd29yZCIsImNzcmZUb2tlbiI6ImJiOTVlYTRmLTlhOGMtNGM5ZS1iOWY0LWMzMTAxZGE5MTVlMyIsImxvZ2luU2lkZSI6InBjIiwiYWxsb3dNdWx0aSI6MCwidmVyc2lvbiI6MiwidXVpZCI6IjNkMTA1YWM2LThmYTAtNDFmYy04OTdlLWUwMjYzNWZlYzQzOCIsInJlZ2lvblRva2VuIjpmYWxzZSwiaWF0IjoxNzA2MjU0MDc0fQ.YhO-L9xI2Am3hd5rRRNBX1d_iGFFgItvx8ANkREIoxU; _login_way=password; _abcyun_token_=eyJhbGciOiJIUzI1NiJ9.eyJlbXBsb3llZUlkIjoiZmZmZmZmZmYwMDAwMDAwMDEzNDRiMWQ4MDZkYWEwMDAiLCJlbXBsb3llZVNob3J0SWQiOiIxMzg4NDMwMTI2NTIyOTk4Nzg0IiwiZW1wbG95ZWVOYW1lIjoi6ams5Yab6IejIiwibG9naW5XYXkiOiJwYXNzd29yZCIsImNsaW5pY0lkIjoiZmZmZmZmZmYwMDAwMDAwMDIwNWQxOTYwMGEyZTQwMDAiLCJjbGluaWNTaG9ydElkIjoiMjMzMjA0ODA4MjMyNjE0Mjk3NiIsImNsaW5pY1R5cGUiOjIsImNoYWluSWQiOiJmZmZmZmZmZjAwMDAwMDAwMWNkM2VhYzAwOWVjZTAwMCIsImNoYWluU2hvcnRJZCI6IjIwNzcyNjE5NjM2NjkwNjk4MjQiLCJ2aWV3TW9kZSI6MCwiaGlzVHlwZSI6MCwiY3NyZlRva2VuIjoiODViMTgyNjEtOTgyZS00MWJkLThiNDItYjk2ZWE2ZGY4YzQzIiwibG9naW5FZGl0aW9uIjoiMjAiLCJsb2dpblNpZGUiOiJwYyIsImFsbG93TXVsdGkiOjAsInZlcnNpb24iOjIsInN1cHBvcnRlZEJ1c2luZXNzIjoxLCJ1dWlkIjoiM2QxMDVhYzYtOGZhMC00MWZjLTg5N2UtZTAyNjM1ZmVjNDM4IiwicmVnaW9uSWQiOiIxIiwicmVnaW9uVG9rZW4iOnRydWUsImlhdCI6MTcwNjI1NDA3NX0.YB-airyGoMsfTZyju0AhrDRMn9dcrZMJwuNhRWgNuME; x-csrf-token=85b18261-982e-41bd-8b42-b96ea6df8c43; _clinic_id=ffffffff00000000205d19600a2e4000; _chain_id=ffffffff000000001cd3eac009ece000; _user_info_={%22id%22:%22ffffffff000000001344b1d806daa000%22}; _is_refresh_token_=TRUE"
    }
    resp = requests.get(url=url, params=params, headers=headers, verify=False).content.decode("utf-8")
    result = json.loads(resp)
    # print(result)
    datas = result['data']
    for data in datas:
        first_class_id = data['type']
        first_class_name = data['name']
        for item in data['items']:
            second_class_id = item['id']
            second_class_name = item['category']
            for child in item['children']:
                three_class_id = child['id']
                three_class_name = child['category']
                print(first_class_id, first_class_name, second_class_id, second_class_name, three_class_id,
                      three_class_name)
                createCategory((first_class_id, first_class_name, second_class_id, second_class_name, three_class_id,
                                three_class_name))


def requestMedicine(categoryId, categoryName, limit=10, offset=0):
    url = "https://www.abcyun.cn/api/v2/sc/cdss/medicine/manual/breif?1702003427475&"
    params = {
        "categoryId": categoryId,
        "limit": limit,
        "offset": offset
    }
    print(params)
    random_header = random.choice(headersChoice)
    headers = {
        "Cookie": "uuid=96d48a556bb74cc5933e675387093fc2; Hm_lvt_0e76138257ab1ae8bc1a9375b4180e64=1701757633; __root_domain_v=.abcyun.cn; _qddaz=QD.619501757633326; v2_remember_login=TRUE; grayflag=; grayflag=; Qs_lvt_359035=1701757633%2C1702949563%2C1706254064; Qs_pv_359035=777405993795203700%2C1627377586031348000%2C1580138439060517000%2C4135517799293870000%2C39527485949813740; _global_token_=eyJhbGciOiJIUzI1NiJ9.eyJlbXBsb3llZUlkIjoiZmZmZmZmZmYwMDAwMDAwMDEzNDRiMWQ4MDZkYWEwMDAiLCJlbXBsb3llZVNob3J0SWQiOiIxMzg4NDMwMTI2NTIyOTk4Nzg0IiwiZW1wbG95ZWVOYW1lIjoi6ams5Yab6IejIiwibG9naW5XYXkiOiJwYXNzd29yZCIsImNzcmZUb2tlbiI6ImJiOTVlYTRmLTlhOGMtNGM5ZS1iOWY0LWMzMTAxZGE5MTVlMyIsImxvZ2luU2lkZSI6InBjIiwiYWxsb3dNdWx0aSI6MCwidmVyc2lvbiI6MiwidXVpZCI6IjNkMTA1YWM2LThmYTAtNDFmYy04OTdlLWUwMjYzNWZlYzQzOCIsInJlZ2lvblRva2VuIjpmYWxzZSwiaWF0IjoxNzA2MjU0MDc0fQ.YhO-L9xI2Am3hd5rRRNBX1d_iGFFgItvx8ANkREIoxU; _login_way=password; _abcyun_token_=eyJhbGciOiJIUzI1NiJ9.eyJlbXBsb3llZUlkIjoiZmZmZmZmZmYwMDAwMDAwMDEzNDRiMWQ4MDZkYWEwMDAiLCJlbXBsb3llZVNob3J0SWQiOiIxMzg4NDMwMTI2NTIyOTk4Nzg0IiwiZW1wbG95ZWVOYW1lIjoi6ams5Yab6IejIiwibG9naW5XYXkiOiJwYXNzd29yZCIsImNsaW5pY0lkIjoiZmZmZmZmZmYwMDAwMDAwMDIwNWQxOTYwMGEyZTQwMDAiLCJjbGluaWNTaG9ydElkIjoiMjMzMjA0ODA4MjMyNjE0Mjk3NiIsImNsaW5pY1R5cGUiOjIsImNoYWluSWQiOiJmZmZmZmZmZjAwMDAwMDAwMWNkM2VhYzAwOWVjZTAwMCIsImNoYWluU2hvcnRJZCI6IjIwNzcyNjE5NjM2NjkwNjk4MjQiLCJ2aWV3TW9kZSI6MCwiaGlzVHlwZSI6MCwiY3NyZlRva2VuIjoiODViMTgyNjEtOTgyZS00MWJkLThiNDItYjk2ZWE2ZGY4YzQzIiwibG9naW5FZGl0aW9uIjoiMjAiLCJsb2dpblNpZGUiOiJwYyIsImFsbG93TXVsdGkiOjAsInZlcnNpb24iOjIsInN1cHBvcnRlZEJ1c2luZXNzIjoxLCJ1dWlkIjoiM2QxMDVhYzYtOGZhMC00MWZjLTg5N2UtZTAyNjM1ZmVjNDM4IiwicmVnaW9uSWQiOiIxIiwicmVnaW9uVG9rZW4iOnRydWUsImlhdCI6MTcwNjI1NDA3NX0.YB-airyGoMsfTZyju0AhrDRMn9dcrZMJwuNhRWgNuME; x-csrf-token=85b18261-982e-41bd-8b42-b96ea6df8c43; _clinic_id=ffffffff00000000205d19600a2e4000; _chain_id=ffffffff000000001cd3eac009ece000; _user_info_={%22id%22:%22ffffffff000000001344b1d806daa000%22}; _is_refresh_token_=TRUE",
        'Accept': '*/*',
        'User-Agent': random_header,
        'Accept-Encoding': 'gzip, deflate, sdch',
        'Accept-Language': 'zh-CN,zh;q=0.8'
    }
    proxy = '127.0.0.1:7890'
    proxies = {
        'https': 'http://' + proxy,  # 查找到vpn在本机使用的http代理端口
        'http': 'http://' + proxy,  # 查找到你的vpn在本机使用的https代理端口
    }
    resp = requests.get(url=url, params=params, headers=headers, proxies=proxies, verify=False).content.decode("utf-8")
    result = json.loads(resp)
    try:
        breifs = result['data']['breifs']
    except Exception as e:
        print(result)
        raise Exception(e)

    if is_empty(breifs):
        updateCategoryFlag(categoryId, 1)
        return
    for breif in breifs:
        medicine_id = breif['id']
        name = breif['name']
        spec = breif['spec']
        manufacturer = breif['manufacturer']
        if getMedicineById(categoryId, medicine_id):
            print(f'categoryId={categoryId}, medicine_id={medicine_id}, name={name}, 此数据已爬取，直接跳过')
            continue
        # tags = requestMedicineDetial(medicine_id)
        print(medicine_id, name, spec, manufacturer, limit, offset, "")
        createMedicines(
            (categoryId, categoryName, medicine_id, name, spec if spec is not None else "", manufacturer, ""))
    # 记录爬取页数
    updateCategoryPage(categoryId, json.dumps({"limit": limit, "offset": offset}))
    offset = offset + 10
    time.sleep(random.randrange(5, 10))
    requestMedicine(categoryId, categoryName, limit, offset)


def requestMedicineDetial(id):
    time.sleep(random.randrange(5, 10))
    if is_empty(id):
        return ""
    url = "https://www.abcyun.cn/api/v2/sc/cdss/medicine/manual?1702002025011"
    params = {
        "id": id,
    }
    # print(cookieVal)
    random_header = random.choice(headersChoice)
    headers = {
        "Cookie": cookieVal,
        'Accept': '*/*',
        'User-Agent': random_header,
        'Accept-Encoding': 'gzip, deflate, sdch',
        'Accept-Language': 'zh-CN,zh;q=0.8'
    }
    proxy = '127.0.0.1:2080'
    proxies = {
        'https': 'http://' + proxy,  # 查找到vpn在本机使用的http代理端口
        'http': 'http://' + proxy,  # 查找到你的vpn在本机使用的https代理端口
    }
    # , proxies = proxies
    resp = requests.get(url=url, params=params, headers=headers, verify=False).content.decode("utf-8")
    result = json.loads(resp)
    try:
        result = result['data']['items']
        return json.dumps(result, ensure_ascii=False)
    except Exception as e:
        print(result)
        time.sleep(100)
        raise Exception(e)


def createCategory(category):
    sql = (
        "insert into my_abc_categorys_2023128 (first_class_id, first_class_name, second_class_id, second_class_name, "
        "three_class_id, three_class_name)"
        "value(%s, %s, %s, %s, %s, %s)")
    cursor.execute(sql, category)
    db.commit()


def createMedicines(medicine):
    sql = ("insert into my_abc_medicines (catalog_id, category, medicine_id, name, spec, manufacturer, tags) value(%s, "
           "%s, %s, %s, %s, %s, %s)")
    cursor.execute(sql, medicine)
    db.commit()


def getMedicineById(catalog_id, medicine_id):
    sql = "select * from my_abc_medicines where catalog_id='%s' and medicine_id='%s'" % (catalog_id, medicine_id)
    cursor.execute(sql)
    result = cursor.fetchall()
    return result


def getMedicineTagsIsEmpty():
    sql = "SELECT a.first_class_name, b.* " \
          "FROM my_abc_categorys_2023128 AS a LEFT JOIN my_abc_medicines AS b ON a.three_class_id=b.catalog_id " \
          "WHERE a.first_class_id=1 AND (b.tags IS NULL OR b.tags='') LIMIT 1000"
    cursor.execute(sql)
    result = cursor.fetchall()
    return result


def getCategorys():
    sql = "select * from my_abc_categorys_2023128 where flag=0"
    cursor.execute(sql)
    result = cursor.fetchall()
    for res in result:
        if not isinstance(res, dict):
            continue
        three_class_id = res["three_class_id"]
        three_class_name = res["three_class_name"]
        flg = res["flag"]
        page = json.loads(res["page"]) if res["page"] else ''
        if is_empty(page):
            page = {"limit": 10, "offset": 0}
        requestMedicine(three_class_id, three_class_name, page['limit'], page['offset'])
        print(three_class_id, three_class_name, page)
        time.sleep(random.randrange(3, 10))


def updateCategoryFlag(three_class_id, flag):
    sql = "update my_abc_categorys_2023128 set flag=%s where three_class_id='%s'" % (flag, three_class_id)
    cursor.execute(sql)
    db.commit()


def updateCategoryPage(three_class_id, page):
    sql = "update my_abc_categorys_2023128 set page='%s' where three_class_id='%s'" % (page, three_class_id)
    cursor.execute(sql)
    db.commit()


def updateMedicineTags(tags, id):
    sql = "update my_abc_medicines set tags=%s where id=%s"
    cursor.execute(sql, (tags, id))
    db.commit()


def handle():
    rs = getMedicineTagsIsEmpty()
    for item in rs:
        # print(item)
        if isinstance(item, dict):
            medicine_id = item['medicine_id']
        else:
            medicine_id = 0
        if medicine_id == 0:
            continue
        tags = requestMedicineDetial(medicine_id)
        if is_empty(tags):
            continue
        tags = str.replace(tags, "3.1415926", "")
        updateMedicineTags(tags, item['id'])
        print(item['id'], item['name'], tags)


def is_empty(s):
    """
    判断字符串是否为空
    :param s:
    :return:
    """
    if s is None:
        return True
    elif isinstance(s, str) and not s.strip():
        return True
    elif isinstance(s, dict) and len(s) == 0:
        return True
    elif isinstance(s, list) and len(s) == 0:
        return True
    elif isinstance(s, tuple) and len(s) == 0:
        return True
    else:
        return False


def readIniFile(fileName):
    # config = configparser.ConfigParser()
    # config.read('config.ini')
    # print(config['cookie']['cookie'])
    pass


def readTxtFile(fileName):
    file_path = Path(fileName)
    if not file_path.is_file():
        raise FileNotFoundError("File does not")

    with file_path.open(mode='r', encoding='utf-8') as f:
        content = f.read()
        return content


if __name__ == "__main__":
    cookieFileName = "cookie.txt"
    currentDir = os.path.dirname(os.path.abspath(__file__))
    cookieVal = readTxtFile(os.path.join(currentDir, cookieFileName))
    handle()
    # getCategorys()
    # requestMedicine("ad4fc9614b8a4a4f85a8966e7c44d55e", 10, 0)
    # requestMedicineDetial("04ae8dcebe394085b44d48067fe43708")

